package database;

import inventory.DiscoverItem;
import inventory.InventoryItem;
import java.io.IOException;
import java.net.URL;
import java.security.MessageDigest;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;
import user.User;

/**
 * Database class implements the models that acts as the corresponding
 * implementations from the user views with the Lithub database.
 *
 * @author Reynaldo Gonzales.
 * @version 1.0
 *
 */
public class Database {

    private static Database instance = null;
    private static Connection connection;
    private Statement st = null;
    private ResultSet rs = null;

    /**
     * Private database constructor that creates a connection from an
     * instantiated model with the Lithub database.
     *
     * @throws SQLException: ClassNotFoundException, InstantiationException, and
     * IllegalAccessException.
     *
     */
    private Database() throws ClassNotFoundException, InstantiationException, IllegalAccessException {
        try {
            String username = "root";
            String password = "baconatmidnight";
            String url = "jdbc:mysql://localhost:37965/lithub";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * getDatabase retrieves an instance of a connected database. @catch
     * SQLException: ClassNotFoundException, InstantiationException, and
     * IllegalAccessException.
     *
     * @return instance of database connection.
     *
     */
    public static Database getDatabase() {
        if (instance == null) {
            try {
                instance = new Database();
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            } catch (InstantiationException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IllegalAccessException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return instance;
    }

    /**
     * encryptPassword encrypts a passed password parameter with SHA-1
     * encryption, if available. @catch String based password that needs to be
     * encrypted.
     *
     * @return an encrypted password.
     *
     */
    private String encryptPassword(String password, String username) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-1");
            String clearPassword = password + username;
            md.update(clearPassword.getBytes());
            byte[] digestedPassword = md.digest();
            return new String(digestedPassword);
        } catch (java.security.NoSuchAlgorithmException e) {
            System.out.println("MD5 doesn't exist");
            System.out.println(e.toString());
            return null;
        }
    }

    /**
     * addAccountToDatabase adds a user object to the Lithub account's table in
     * the Lithub database, giving the user object a Lithub account.
     *
     * @param user object that needs to be added to the database. @catch
     * SQLException: A generic SQL exeption error message.
     *
     */
    public void addAccountToDatabase(User u) {
        //String encrypt the password
        u.password = encryptPassword(u.password, u.username);

        try {
            st = connection.createStatement();

            String updateString = "INSERT INTO accounts (username, password, email) VALUES (?,?,?)";
            PreparedStatement statement = connection.prepareStatement(updateString);
            statement.setString(1, u.username);
            statement.setString(2, u.password);
            statement.setString(3, u.email);
            statement.executeUpdate();

            String queryString = "SELECT * FROM accounts WHERE username = '" + u.username + "'";
            rs = st.executeQuery(queryString);
            if (rs.next()) {
                u.id = rs.getInt(1);
            }
        } catch (Exception ex) {
            System.out.print(ex.toString());
        }
    }

    /**
     * getUserByUsernameAndPassword retrieves a user object based on passed
     * username and password parameters.
     *
     * @param a String based username.
     * @param b String based password
     * @throws SQLException: A generic SQL exeption error message.
     * @return User object that represent a user's account.
     *
     */
    public User getUserByUsernameAndPassword(String username, String password) throws SQLException {
        String encryptedPassword = encryptPassword(password, username);
        int id = -1;
        String email = "";

        String updateString = "SELECT * FROM accounts WHERE username=? AND password=?";
        PreparedStatement statement = connection.prepareStatement(updateString);
        statement.setString(1, username);
        statement.setString(2, encryptedPassword);
        rs = statement.executeQuery();

        if (rs.next()) {
            id = rs.getInt(1);
            email = rs.getString(4);
        }

        User u = new User(id, username, password, email);

        return u;
    }

    /**
     * getLibrary returns a mapping of a user object's library, comprised of
     * inventory objects.
     *
     * @catch SQLException: A generic SQL exeption error.
     *
     * @return user object's library; comrpised of inventory objects.
     *
     */
    public Map<InventoryItem, ArrayList<User>> getLibrary() {
        Map<InventoryItem, ArrayList<User>> library = new HashMap<InventoryItem, ArrayList<User>>();

        try {
            st = connection.createStatement();
            ResultSet resultSet = st.executeQuery("select * from userinventory JOIN items ON items.itemID = userinventory.itemID");
            while (resultSet.next()) {
                String itemCategory = resultSet.getString(7);
                String itemName = resultSet.getString(8);
                String itemNameLong = resultSet.getString(9);
                String itemAuthor = resultSet.getString(10);
                String itemPublisher = resultSet.getString(11);
                String itemISBN = resultSet.getString(12);
                String itemSummary = resultSet.getString(13);
                int itemID = resultSet.getInt(2);
                int userID = resultSet.getInt(3);
                int status = resultSet.getInt(4);
                int ownerID = resultSet.getInt(5);

                User u = getUserByID(userID);

                if (u.id != -1) {
                    InventoryItem i = null;
                    for (InventoryItem key : library.keySet()) {
                        if (key.itemISBN.equals(itemISBN)) {
                            i = key;
                        }
                    }
                    if (i == null) {
                        i = new InventoryItem(itemID, userID, status, ownerID,
                                itemCategory, itemName, itemNameLong,
                                itemAuthor, itemPublisher, itemISBN,
                                itemSummary);
                        ArrayList<User> listOfOwners = new ArrayList<User>();
                        listOfOwners.add(u);
                        library.put(i, listOfOwners);
                    } else {
                        library.get(i).add(u);
                    }
                }
            }


        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return library;
    }

    /**
     * populateUserInventory uses the Lithub database to create an array of
     * books a user has in their inventory based off of the passed user object
     * parameter.
     *
     * @param User object that refers to a specific user's account. @catch
     * SQLException: A generic SQL exeption error message.
     *
     */
    public void populateUserInventory(User u) {
        u.clearInventory();
        try {
            //Connect to the databse.
            st = connection.createStatement();

            rs = st.executeQuery("SELECT * FROM items "
                    + "JOIN userinventory ON userinventory.itemID = items.itemID "
                    + "WHERE ownerID=" + u.id + " OR userID=" + u.id);

            while (rs.next()) {
                String itemCategory = rs.getString(2);
                String itemName = rs.getString(3);
                String itemNameLong = rs.getString(4);
                String itemAuthor = rs.getString(5);
                String itemPublisher = rs.getString(6);
                String itemISBN = rs.getString(7);
                String itemSummary = rs.getString(8);
                int itemID = rs.getInt(10);
                int userID = rs.getInt(11);
                int status = rs.getInt(12);
                int ownerID = rs.getInt(13);
                InventoryItem i = new InventoryItem(itemID, userID, status, ownerID,
                        itemCategory, itemName, itemNameLong,
                        itemAuthor, itemPublisher, itemISBN,
                        itemSummary);
                u.addItemToInventoryArray(i);
            }
        } catch (SQLException ex) {
            //Something happens.
        }
    }

    /**
     * getUserByID retrieves a user objected based on the passed user id number.
     *
     * @param User integer that refers to a specific user's ID number and
     * account. @catch SQLException: A generic SQL exeption error message.
     *
     * @catch SQLException; generic SQLException error.
     * @return user object found by username based retrieval.
     *
     */
    public User getUserByID(int id) {

        User u = new User(-1, null, null, null);

        try {
            st = connection.createStatement();
            rs = st.executeQuery("SELECT * FROM accounts WHERE userID = " + id);
            if (rs.next()) {
                u.id = rs.getInt(1);
                u.username = rs.getString(2);
                u.password = rs.getString(3);
                u.email = rs.getString(4);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return u;
    }

    /**
     * getUserByUsername retrieves a user objected based on the passed username
     * parameter.
     *
     * @param Username string that refers to a specific user's username and
     * account. @catch SQLException: A generic SQL exeption error message.
     * @return user object found by username based retrieval.
     *
     */
    public User getUserByUsername(String username) {
        User u = new User(-1, null, null, null);

        try {
            st = connection.createStatement();
            rs = st.executeQuery("SELECT * FROM accounts WHERE username = '" + username + "'");
            if (rs.next()) {
                u.id = rs.getInt(1);
                u.username = rs.getString(2);
                u.password = rs.getString(3);
                u.email = rs.getString(4);


            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return u;
    }

    /**
     * populateUserFriendList uses the Lithub database to create an array of
     * user friend user objects based off of the passed user object parameter.
     *
     * @param User object that refers to a specific user's account.
     *
     * @catch Exception; generic Exception exception error.
     *
     */
    public void populateUserFriendList(User u) {
        u.clearFriendList();

        try {
            //Connect to the databse.
            st = connection.createStatement();

            rs = st.executeQuery("SELECT * FROM accounts WHERE userID = " + u.id);

            if (rs.next()) {
                String friendList = rs.getString(5);
                StringTokenizer tokenizedInput = new StringTokenizer(friendList, " ");
                while (tokenizedInput.hasMoreTokens()) {
                    String key = tokenizedInput.nextToken();
                    User friend = getUserByID(Integer.parseInt(key));
                    populateUserInventory(friend);
                    u.addFriendToFriendList(friend);
                }
            }
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }
    }

    /**
     * lendItem implements part of the trading of an inventory item between two
     * user accounts - the reciever of the item has the item listed in their
     * inventory as 'borrowed' while the lender has the same item listed as
     * 'lent' out.
     *
     * @param a an inventory item ID that designates which item should be
     * borrowed/lent between user accounts.
     * @param b a user object that designates which user is lending the
     * inventory item to a borrower.
     * @param c a user object that designates which user is borrowing the
     * inventory as a reciever. @catch SQLException: A generic SQL exeption
     * error message.
     * 
     * @catch Exception; generic Exception exception error.
     *
     */
    public void lendItem(InventoryItem i, User sender, User receiver) {
        try {
            // Change the status of the sender's item and set the userID of it to the receiver's ID
            String updateString = "UPDATE userinventory SET status=102, userID = ?  WHERE ownerID = ? AND itemID = ?";
            PreparedStatement statement = connection.prepareStatement(updateString);
            statement.setInt(1, receiver.id);
            statement.setInt(2, sender.id);
            statement.setInt(3, i.itemID);
            statement.executeUpdate();

        } catch (Exception ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * returnItem implements part of the the trading of an inventory item
     * between two user accounts - the reciever of the item has the item listed
     * in their inventory as 'owned' and the returner has the item removed from
     * their library.
     *
     * @param a an inventory item ID that designates which item should be
     * returned between user accounts.
     * @param b a user object that designates which user the return-ee of the
     * inventory object - this user object recieves the returned inventory
     * object.
     * 
     * @catch Exception; generic Exception exception error.
     *
     */
    public void returnItem(InventoryItem i, User owner) {
        try {
            // Change the status of the sender's item and set the userID of it to the receiver's ID
            String updateString = "UPDATE userinventory SET status=101, userID = ?  WHERE ownerID = ? AND itemID = ?";
            PreparedStatement statement = connection.prepareStatement(updateString);
            statement.setInt(1, owner.id);
            statement.setInt(2, owner.id);
            statement.setInt(3, i.itemID);
            statement.executeUpdate();

        } catch (Exception ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * getIventoryItemByISBN implements the retrieval of an inventory object
     * from a user's inventory based on the passed ISBN parameter.
     *
     * @param a an ISBN string that refers to a book that should be retrieved
     * from a user's inventory. @throw IOException, SAXException,
     * ParserConfigurationException @catch SQLException: A generic SQL exeption
     * error message.
     * @return the requested item inventory object.
     *
     */
    public InventoryItem getIventoryItemByISBN(String ISBN) throws IOException, SAXException, ParserConfigurationException {
        InventoryItem i = null;
        try {
            // Get the item from the database
            // If it's not in there, get the info
            // from the ISBN Database
            rs = st.executeQuery("SELECT * FROM items WHERE itemISBN='" + ISBN + "'");
            if (rs.next()) {
                int itemID = rs.getInt(1);
                int userID = -1;
                int status = -1;
                int ownerID = -1;
                String itemCategory = rs.getString(2);
                String itemName = rs.getString(3);
                String itemNameLong = rs.getString(4);
                String itemAuthor = rs.getString(5);
                String itemPublisher = rs.getString(6);
                String itemSummary = rs.getString(8);

                i = new InventoryItem(itemID, userID, status, ownerID,
                        itemCategory, itemName, itemNameLong,
                        itemAuthor, itemPublisher, ISBN, itemSummary);
            } else {
                String urlString = "http://isbndb.com/api/books.xml?access_key=MIIRXTQX&results=texts&index1=isbn&value1=" + ISBN;
                URL url = new URL(urlString);

                DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
                DocumentBuilder db = dbf.newDocumentBuilder();
                Document doc = db.parse(url.openStream());

                int itemID = -1;
                int userID = -1;
                int status = -1;
                int ownerID = -1;

                String itemCategory = "book";

                String itemName = "";
                if (doc.getElementsByTagName("Title").item(0).getChildNodes().item(0) != null) {
                    itemName = doc.getElementsByTagName("Title").item(0).getChildNodes().item(0).getNodeValue();
                }

                String itemNameLong = "";
                if (doc.getElementsByTagName("TitleLong").item(0).getChildNodes().item(0) != null) {
                    itemNameLong = doc.getElementsByTagName("TitleLong").item(0).getChildNodes().item(0).getNodeValue();
                }

                String itemAuthor = "";
                if (doc.getElementsByTagName("AuthorsText").item(0).getChildNodes().item(0) != null) {
                    itemAuthor = doc.getElementsByTagName("AuthorsText").item(0).getChildNodes().item(0).getNodeValue();
                }

                String itemPublisher = "";
                if (doc.getElementsByTagName("PublisherText").item(0).getChildNodes().item(0) != null) {
                    itemPublisher = doc.getElementsByTagName("PublisherText").item(0).getChildNodes().item(0).getNodeValue();
                }
                String itemSummary = "";
                if (doc.getElementsByTagName("Summary").item(0).getChildNodes().item(0) != null) {
                    itemSummary = doc.getElementsByTagName("Summary").item(0).getChildNodes().item(0).getNodeValue();
                }
                i = new InventoryItem(itemID, userID, status, ownerID,
                        itemCategory, itemName, itemNameLong,
                        itemAuthor, itemPublisher, ISBN, itemSummary);
            }
        } catch (Exception ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }

    /**
     * getInventoryItemByTitle returns an inventory item object based on a
     * passed string title.
     *
     * @param a String based title name.
     * 
     * @catch Exception; generic Exception exception error.
     * @return an inventory item object.
     *
     */
    public InventoryItem getInventoryItemByTitle(String title) {
        InventoryItem i = null;
        try {
            // Get the item from the database
            // If it's not in there, get the info
            // from the ISBN Database
            String query = "SELECT * FROM items WHERE itemName=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, title);
            rs = statement.executeQuery();
            if (rs.next()) {
                int itemID = rs.getInt(1);
                int userID = -1;
                int status = -1;
                int ownerID = -1;
                String itemCategory = rs.getString(2);
                String itemNameLong = rs.getString(4);
                String itemAuthor = rs.getString(5);
                String itemPublisher = rs.getString(6);
                String itemISBN = rs.getString(7);
                String itemSummary = rs.getString(8);

                i = new InventoryItem(itemID, userID, status, ownerID,
                        itemCategory, title, itemNameLong,
                        itemAuthor, itemPublisher, itemISBN, itemSummary);
            }
        } catch (Exception ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }

    /**
     * insertInventoryItemIntoDatabase implements the addition of an inventory
     * object
     *
     * @param a an InventoryItem object that refers to a book that should added
     * to the Lithub database. @catch SQLException: A generic SQL exeption error
     * message.
     * 
     * @catch SQLException; generic SQLException exception error.
     * @return the inventory object data that was added the database.
     *
     */
    public InventoryItem insertInventoryItemIntoDatabase(InventoryItem i) {
        try {
            String updateString = "INSERT INTO items (itemCategory, itemName, itemNameLong, "
                    + "itemAuthor, itemPublisher, itemISBN, itemSummary) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement statement = connection.prepareStatement(updateString);
            statement.setString(1, i.itemCategory);
            statement.setString(2, i.itemName);
            statement.setString(3, i.itemNameLong);
            statement.setString(4, i.itemAuthor);
            statement.setString(5, i.itemPublisher);
            statement.setString(6, i.itemISBN);
            statement.setString(7, i.itemSummary);
            statement.executeUpdate();

            String queryString = "SELECT * FROM items WHERE itemISBN='" + i.itemISBN + "'";

            rs = st.executeQuery(queryString);

            if (rs.next()) {
                i.itemID = rs.getInt(1);

            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }

        return i;
    }

    /**
     * AddInventoryItemToUser implements the addition of an item to a user's
     * inventory based on a passed ISBN number.
     *
     * @param a an ISBN string that refers to a book that should be added to a
     * user's inventory. @catch SQLException: A generic SQL exeption error
     * message.
     *
     */
    public void AddInventoryItemToUser(InventoryItem i, User u) {
        int status = 101;   //If a user is adding an item to their inventory, it's assumed they currently own it.

        try {
            rs = st.executeQuery("SELECT * FROM userinventory WHERE userID = " + u.id + " AND itemID = " + i.itemID);

            if (rs.next() == false) {
                String updateString = "INSERT INTO userinventory (itemID, userID, status, ownerID) "
                        + "VALUES (?, ?, ?, ?)";

                PreparedStatement statement = connection.prepareStatement(updateString);
                statement.setInt(1, i.itemID);
                statement.setInt(2, u.id);
                statement.setInt(3, 101);
                statement.setInt(4, u.id);
                statement.executeUpdate();
            }

        } catch (SQLException ex) {
            //Something happens.
        }
    }
}